﻿using KPIS.GERP.Models;
using KPIS.GERP.Models.Class;
using KPIS.GERP.Models.ICM;
using KPIS.GERP.Models.SYS;
using System;
using System.Data;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Linq.Dynamic;
using System.ServiceModel;
using System.Web.Mvc;
using KPIS.DBM;
using System.Configuration;

namespace KPIS.GERP.WCF.GIMS
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "DisposeService" in code, svc and config file together.
    // NOTE: In order to launch WCF Test Client for testing this service, please select DisposeService.svc or DisposeService.svc.cs at the Solution Explorer and start debugging.
    [ServiceContract]
    public interface IDisposeService
    {
        #region SelectListItem

        // List ประเภทการจำหน่าย เช่น โอน, ทำลาย, จำหน่ายเป็นสูญ, บริจาค
        [OperationContract]
        List<SelectListItem> SelectListDisposeType(IcmMasLookUpType.LookUpType lookUpType);

        // List ประเภทพัสดุ เช่น วัสดุ, ครุภัณฑ์
        [OperationContract]
        List<SelectListItem> SelectListResourceType();

        // List จากหน่วยงาน
        [OperationContract]
        List<SelectListItem> SelectListJobsite();

        // List จากคลัง
        [OperationContract]
        List<SelectListItem> SelectListWarehouse();
        
        #endregion

        #region Insert / Update / Delete >>> ตาราง IcmDispose

        [OperationContract]
        int Insert(Dispose model, int intCreateByUserId);

        [OperationContract]
        void Update(Dispose model, int updateByUserId);

        [OperationContract]
        void Delete(int id, int deleteByUserId);

        #endregion

        #region Create DisposeOfficeSupplylotHistory กรณีที่พัสดุเป็น "วัสดุ"

        // เมื่อเลือกรายการพัสดุที่ต้องการจำหน่ายแล้ว ก็จะ insert ข้อมูลลงในตาราง IcmDisposeOfficeSupplylotHistory
        [OperationContract]
        int InsertDisposeOfficeSupplylotHistory(DisposeOfficeSupplylotHistory model, int intCreateByUserId);

        // เมื่อเลือกรายการพัสดุที่ต้องการจำหน่ายแล้ว ก็จะอัพเดท RecordStatus ในตาราง IcmOfficeSuppliesLot เป็น "W"
        [OperationContract]
        void UpdateOfficeSuppliesLot(OfficeSuppliesLot model, int updateByUserId);

        #endregion

        #region Create MovementHistory and Update IcmAsset กรณีที่พัสดุเป็น "ครุภัณฑ์"

        // เมื่อเลือกรายการพัสดุที่ต้องการจำหน่ายแล้ว ก็จะ insert ข้อมูลลงในตาราง IcmMovementHistory โดยที่ MovementType = Dispose
        [OperationContract]
        int InsertMovementHistory(MovementHistory model, int intCreateByUserId);

        // เมื่อเลือกรายการพัสดุที่ต้องการจำหน่ายแล้ว ก็จะอัพเดท RecordStatus ในตาราง IcmAsset เป็น "W"
        [OperationContract]
        void UpdateAsset(Asset model, int updateByUserId);

        #endregion

        #region Approved

        [OperationContract]
        void Approved(int DisposeId, int approvedByUserId);

        #endregion

        // แสดงใบจำหน่าย จากตาราง IcmDispose
        [OperationContract]
        IEnumerable<Dispose> SelectDispose(Dispose criteriaModel = null, List<string> includeSubObjectString = null);

        // แสดงรายการวัสดุ เพื่อที่จะเลือกจำหน่าย
        [OperationContract]
        IEnumerable<OfficeSuppliesLot> SelectOfficeSuppliesLot(int ResTypeId, int? FromWhId, List<string> includeSubObjectString = null);

        // แสดงรายการครุภัณฑ์ เพื่อที่จะเลือกจำหน่าย
        [OperationContract]
        IEnumerable<Asset> SelectAsset(int ResTypeId, int? FromWhId, int? FromJobsiteId, List<string> includeSubObjectString = null);

        // แสดงรายการ Item ที่ได้เลือกจำหน่ายไปแล้ว  จากตาราง IcmDisposeOfficeSupplylotHistory  >>>  กรณีที่พัสดุเป็น "วัสดุ"
        [OperationContract]
        IEnumerable<DisposeOfficeSupplylotHistory> SelectDisposeOfficeSupplylotHistory(DisposeOfficeSupplylotHistory criteriaModel = null, List<string> includeSubObjectString = null);

        // แสดงรายการ Item ที่ได้เลือกจำหน่ายไปแล้ว  จากตาราง IcmMovementHistory  >>>  กรณีที่พัสดุเป็น "ครุภัณฑ์"
        [OperationContract]
        IEnumerable<MovementHistory> SelectMovementHistory(MovementHistory criteriaModel = null, List<string> includeSubObjectString = null);

        // ยกเลิกรายการพัสดุที่เลือก
        [OperationContract]
        void UnSelect(int id, string ResourceType, int updateByUserId);

        // ตรวจสอบว่าใบจำหน่ายแต่ละใบว่ามี "คณะกรรมการจำหน่ายพัสดุ" แล้วหรือยัง
        [OperationContract]
        bool IsCommittee(int DisposeId);
    }
    
    public class DisposeService : IDisposeService
    {
        #region SelectListItem

        // List ประเภทการจำหน่าย เช่น โอน, ทำลาย, จำหน่ายเป็นสูญ, บริจาค
        public List<SelectListItem> SelectListDisposeType(IcmMasLookUpType.LookUpType lookUpType)
        {
            var data = new List<SelectListItem>();

            var context = new GERPContext(false);
            try
            {
                List<Models.ICM.IcmMasLookUp> listRepairStatus = (from a in context.IcmMasLookUps
                                                                  where a.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                                                                  && a.LookUpTypeId == (int)lookUpType
                                                                  select a).ToList();

                if (listRepairStatus.Count > 0)
                {
                    foreach (var RepairStatus in listRepairStatus)
                    {
                        var item = new SelectListItem { Text = RepairStatus.LookUpName, Value = RepairStatus.LookUpId.ToString() };
                        data.Add(item);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return data;
        }

        // List ประเภทพัสดุ เช่น วัสดุ, ครุภัณฑ์
        public List<SelectListItem> SelectListResourceType()
        {
            var data = new List<SelectListItem>();

            var context = new GERPContext(false);
            try
            {
                List<Models.ICM.MasResourceType> listResourceType = (from a in context.MasResourceTypes
                                                        where a.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                                                        && a.Code == "1" || a.Code == "2"
                                                        select a).ToList();

                if (listResourceType.Count > 0)
                {
                    foreach (var ResourceType in listResourceType)
                    {
                        var item = new SelectListItem { Text = ResourceType.Name, Value = ResourceType.ResTypeId.ToString() };
                        data.Add(item);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return data;
        }

        // List จากหน่วยงาน
        public List<SelectListItem> SelectListJobsite()
        {
            var data = new List<SelectListItem>();
            data.Add(new SelectListItem { Selected = true, Text = "ไม่ระบุ", Value = "" });

            var context = new GERPContext(false);
            try
            {
                List<Models.HRM.JobSite> listJobsite = (from a in context.JobSites
                                                        where a.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                                                        select a).ToList();

                if (listJobsite.Count > 0)
                {
                    foreach (var Jobsite in listJobsite)
                    {
                        var item = new SelectListItem { Text = Jobsite.Name, Value = Jobsite.JobSiteId.ToString() };
                        data.Add(item);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return data;
        }

        // List จากคลัง
        public List<SelectListItem> SelectListWarehouse()
        {
            var data = new List<SelectListItem>();
            data.Add(new SelectListItem { Selected = true, Text = "ไม่ระบุ", Value = "" });

            var context = new GERPContext(false);
            try
            {
                List<Models.ICM.Warehouse> listWarehouse = (from a in context.Warehouses
                                                            where a.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                                                            select a).ToList();

                if (listWarehouse.Count > 0)
                {
                    foreach (var Warehouse in listWarehouse)
                    {
                        var item = new SelectListItem { Text = Warehouse.Name, Value = Warehouse.WhId.ToString() };
                        data.Add(item);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return data;
        }
        
        #endregion

        #region Insert / Update / Delete >>> ตาราง IcmDispose

        public int Insert(Dispose model, int intCreateByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    model.AssetCode = KPIS.GERP.Models.Class.CodeGenerate.GetNextCode(CodeGenerate.TransectionType.PrePurchaseRequest);
                    model.CreatedBy = intCreateByUserId;
                    model.DisposeStatus = RecordStatus.WaitStatus;
                    model.RecordStatus = RecordStatus.UsedStatus;
                    model.CreatedWhen = DateTime.Now;
                    context.Disposes.Add(model);
                    context.SaveChanges();

                    return model.DisposeId;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        
        public void Update(Dispose model, int updateByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    model.UpdatedBy = updateByUserId;
                    model.UpdatedWhen = DateTime.Now;
                    model.DisposeStatus = RecordStatus.WaitStatus;
                    model.RecordStatus = Models.SYS.RecordStatus.UsedStatus;
                    context.Entry(model).State = EntityState.Modified;

                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }

        public void Delete(int id, int deleteByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    var modelToDelete = context.Disposes.Find(id);
                    if (modelToDelete == null) return;

                    context.Entry(modelToDelete).State = EntityState.Modified;
                    modelToDelete.UpdatedBy = deleteByUserId;
                    modelToDelete.UpdatedWhen = DateTime.Now;
                    modelToDelete.RecordStatus = Models.SYS.RecordStatus.DeletedStatus;
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }
        
        #endregion

        #region Create DisposeOfficeSupplylotHistory กรณีที่พัสดุเป็น "วัสดุ"

        // เมื่อเลือกรายการพัสดุที่ต้องการจำหน่ายแล้ว ก็จะ insert ข้อมูลลงในตาราง IcmDisposeOfficeSupplylotHistory
        public int InsertDisposeOfficeSupplylotHistory(DisposeOfficeSupplylotHistory model, int intCreateByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    model.CreatedBy = intCreateByUserId;
                    model.RecordStatus = RecordStatus.WaitStatus;
                    model.CreatedWhen = DateTime.Now;
                    context.DisposeOfficeSupplylotHistorys.Add(model);
                    context.SaveChanges();

                    return model.DisposeOfficeSupplylotHisId;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        // เมื่อเลือกรายการพัสดุที่ต้องการจำหน่ายแล้ว ก็จะอัพเดท RecordStatus ในตาราง IcmOfficeSuppliesLot เป็น "W"
        public void UpdateOfficeSuppliesLot(OfficeSuppliesLot model, int updateByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    model.UpdatedBy = updateByUserId;
                    model.UpdatedWhen = DateTime.Now;
                    model.RecordStatus = Models.SYS.RecordStatus.WaitStatus;
                    context.Entry(model).State = EntityState.Modified;

                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }

        #endregion

        #region Create MovementHistory and Update IcmAsset กรณีที่พัสดุเป็น "ครุภัณฑ์"

        // เมื่อเลือกรายการพัสดุที่ต้องการจำหน่ายแล้ว ก็จะ insert ข้อมูลลงในตาราง IcmMovementHistory โดยที่ MovementType = Dispose
        public int InsertMovementHistory(MovementHistory model, int intCreateByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    model.CreatedBy = intCreateByUserId;
                    model.RecordStatus = RecordStatus.WaitStatus;
                    model.CreatedWhen = DateTime.Now;
                    context.MovementHistorys.Add(model);
                    context.SaveChanges();

                    return model.MoveHistId;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        // เมื่อเลือกรายการพัสดุที่ต้องการจำหน่ายแล้ว ก็จะอัพเดท RecordStatus ในตาราง IcmAsset เป็น "W"
        public void UpdateAsset(Asset model, int updateByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    model.UpdatedBy = updateByUserId;
                    model.UpdatedWhen = DateTime.Now;
                    model.RecordStatus = Models.SYS.RecordStatus.WaitStatus;
                    context.Entry(model).State = EntityState.Modified;

                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }

        #endregion

        #region Approved

        public void Approved(int DisposeId, int approvedByUserId)
        { 
            try
            {
                using (var context = new GERPContext())
                {
                    // Update DisposeStatus to "A" in IcmDispose
                    var modelDispose = context.Disposes.Where(x => x.DisposeId == DisposeId).Include("MasResourceType").FirstOrDefault();
                    modelDispose.ApprovedBy = approvedByUserId;
                    modelDispose.ApprovedWhen = DateTime.Now;
                    modelDispose.DisposeStatus = Models.SYS.RecordStatus.ApprovedStatus;
                    context.Entry(modelDispose).State = EntityState.Modified;
                    //context.SaveChanges();

                    // ประเภทพัสดุ
                    string ResourceType = modelDispose.MasResourceType.Name;

                    // เช็คว่าประเภทพัสดุว่าเป็นวัสดุ หรือ ครุภัณฑ์ 
                    // ถ้าเป็น "วัสดุ" ก็จะไปอัพเดท 2 ตารางคือ IcmDisposeOfficeSupplylotHistory และ IcmOfficeSuppliesLot
                    // แต่ถ้าเป็น "ครุภัณฑ์" ก็จะไปอัพเดท 2 ตารางคือ IcmMovementHistory และ IcmAsset
                    if (ResourceType.Equals("วัสดุ"))
                    {
                        var modelDisposeOfficeHis = context.DisposeOfficeSupplylotHistorys.Where(x => x.DisposeId == DisposeId && x.RecordStatus != "D").ToList();
                        if (modelDisposeOfficeHis.Count > 0)
                        {
                            for (int i = 0; i < modelDisposeOfficeHis.Count; i++)
                            {
                                // Update Recordstatus to "A" in IcmDisposeOfficeSupplylotHistory  
                                var model = modelDisposeOfficeHis[i];
                                model.UpdatedBy = approvedByUserId;
                                model.UpdatedWhen = DateTime.Now;
                                model.RecordStatus = Models.SYS.RecordStatus.ApprovedStatus;
                                context.Entry(model).State = EntityState.Modified;
                                //context.SaveChanges();

                                // Update Recordstatus to "D" in IcmOfficeSuppliesLot 
                                var modelOfficeSupplyLot = context.OfficeSuppliesLots.Where(x => x.OfficeSuppliesLotId == model.OfficeSuppliesLotId).FirstOrDefault();
                                if (modelOfficeSupplyLot != null)
                                {
                                    modelOfficeSupplyLot.UpdatedBy = approvedByUserId;
                                    modelOfficeSupplyLot.UpdatedWhen = DateTime.Now;
                                    modelOfficeSupplyLot.RecordStatus = Models.SYS.RecordStatus.DeletedStatus;
                                    context.Entry(modelOfficeSupplyLot).State = EntityState.Modified;
                                    //context.SaveChanges();
                                }
                            }
                        }
                    }
                    else
                    {
                        var depreList = new List<Depre>();
                        var modelMovement = context.MovementHistorys.Where(x => x.MoveRefNo == DisposeId && x.MovementType == "Dispose" && x.RecordStatus != "D")
                            .Include("Asset.MasResourceItem.MasResourceSubGroup.MasResourceGroup").ToList();
                        if (modelMovement.Count > 0)
                        {
                            int nBgYear = modelDispose.DisposeDate.Value.Month >= 10 ? modelDispose.DisposeDate.Value.Year + 544 : modelDispose.DisposeDate.Value.Year + 543;
                            var modelMovementHis1 = modelMovement.Where(m => m.Asset.BgSourceId == 1).ToList();
                            var modelMovementHis2 = modelMovement.Where(m => m.Asset.BgSourceId == 11).ToList();
                            int count1 = 0;
                            int count2 = 0;

                            DBManager dbManager = new DBManager(DataProvider.SqlServer, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
                            string strSQL = "";

                            try
                            {
                                dbManager.Open();
                                dbManager.BeginTransaction();

                                #region bgm_source_seq 1
                                if (modelMovementHis1.Count > 0)
                                {
                                    string TranCode = GetNextTranCode("1", nBgYear.ToString(), "G", dbManager);
                                    int TranGrpSeq = GetNextTranGrpSeq(dbManager);
                                    string FiscalPeriod = GetFiscalPeriod(modelDispose.DisposeDate.Value);
                                    string strTranSeq1 = "";

                                    // have Depre
                                    int count = modelMovementHis1.Where(m => !m.Asset.IsLowerAsset).Count();
                                    count1 = count;
                                    if (count > 0)
                                    {
                                        depreList = new List<Depre>();
                                        strSQL = @"
insert into acm_transection
(tran_type, fiscal_period, tran_grp_seq, tran_code, tran_date
, bgm_year, work_area_seq, bgm_source_seq, ref_no, tran_name
, remarks, record_status, created_by, created_when)
values(
'G', @fiscal_period, @tran_grp_seq, @tran_code, @tran_date
, @bgm_year, @work_area_seq, @bgm_source_seq, @ref_no, @tran_name
, @remarks, @record_status, @created_by, @created_when
);
select scope_identity();
";

                                        dbManager.CreateParameters(13);
                                        dbManager.AddParameters(0, "@record_status", "W");
                                        dbManager.AddParameters(1, "@fiscal_period", FiscalPeriod);
                                        dbManager.AddParameters(2, "@tran_grp_seq", TranGrpSeq);
                                        dbManager.AddParameters(3, "@tran_code", TranCode);
                                        dbManager.AddParameters(4, "@tran_date", modelDispose.DisposeDate.Value);
                                        dbManager.AddParameters(5, "@bgm_year", nBgYear);
                                        dbManager.AddParameters(6, "@work_area_seq", null);
                                        dbManager.AddParameters(7, "@bgm_source_seq", 1);
                                        dbManager.AddParameters(8, "@ref_no", TranCode);
                                        dbManager.AddParameters(9, "@tran_name", "บันทึกค่าเสื่อมราคาจากการจำหน่าย [" + modelDispose.DisposeDesc + "]");//modelDispose.DisposeDesc);
                                        dbManager.AddParameters(10, "@remarks", modelDispose.Remark);
                                        dbManager.AddParameters(11, "@created_by", approvedByUserId);
                                        dbManager.AddParameters(12, "@created_when", DateTime.Now);

                                        strTranSeq1 = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();
                                    }

                                    for (int i = 0; i < modelMovementHis1.Count; i++)
                                    {
                                        // Update Recordstatus to "A" in IcmMovementHistory 
                                        var model = modelMovementHis1[i];
                                        model.UpdatedBy = approvedByUserId;
                                        model.UpdatedWhen = DateTime.Now;
                                        model.RecordStatus = Models.SYS.RecordStatus.ApprovedStatus;
                                        context.Entry(model).State = EntityState.Modified;

                                        // Update Recordstatus to "D" in IcmAsset 
                                        var modelAsset = context.Assets.Where(x => x.AssetId == model.AssetId).FirstOrDefault();
                                        if (modelAsset != null)
                                        {
                                            modelAsset.UpdatedBy = approvedByUserId;
                                            modelAsset.UpdatedWhen = DateTime.Now;
                                            modelAsset.RecordStatus = Models.SYS.RecordStatus.DeletedStatus;
                                            context.Entry(modelAsset).State = EntityState.Modified;
                                        }

                                        // Calculate Depre
                                        if (count > 0)
                                        {

                                            dbManager.CreateParameters(1);
                                            dbManager.AddParameters(0, "@AssetId", modelAsset.AssetId);

                                            strSQL = @"
select a.AssetId, a.AssetCode, a.IsLowerAsset
, a.AssetDate, a.Price, a.LifeYear, a.BgSourceId
, grp.AssetAccountId
, grp.DepreAccountId
, grp.CumdepreAccountId
, max(COALESCE(dep.CalculatedDate, a.AssetDate)) as CalculatedDate
, sum(COALESCE(dep.DepAmount, 0)) as DepAmount
from IcmAsset a
left join IcmAssetDepreciation dep on a.AssetId = dep.AssetId
inner join IcmMasResourceItem item on a.ResItemId = item.ResItemId
inner join IcmMasResourceSubGroup sub on item.ResSubGrpId = sub.ResSubGrpId
inner join IcmMasResourceGroup grp on sub.ResGrpId = grp.ResGrpId

where a.AssetId = @AssetId
and a.RecordStatus <> 'D'
and a.IsLowerAsset <> 1

group by a.AssetId, a.AssetCode, a.AssetDate, a.Price, a.LifeYear, a.BgSourceId, a.IsLowerAsset
, grp.AssetAccountId
, grp.DepreAccountId
, grp.CumdepreAccountId";
                                            DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                                            if (dtb.Rows.Count > 0)
                                            {
                                                DataRow dr = dtb.Rows[0];
                                                double Price = 0;// dr["Price"];
                                                double AccumDepreAmount = 0;// main.ConvertNullDouble(dr["DepAmount"]) ?? 0;
                                                double LifeYear = 0;// main.ConvertNullDouble(dr["LifeYear"]) ?? 0;
                                                DateTime LastCalDate = Convert.ToDateTime(dr["CalculatedDate"].ToString());

                                                if (!double.TryParse(dr["Price"].ToString(), out Price))
                                                {
                                                    throw new Exception(dr["AssetCode"].ToString() + " can not parse [Price] to double.");
                                                }

                                                if (!double.TryParse(dr["DepAmount"].ToString(), out AccumDepreAmount))
                                                {
                                                    throw new Exception(dr["AssetCode"].ToString() + " can not parse [DepAmount] to double.");
                                                }

                                                if (!double.TryParse(dr["LifeYear"].ToString(), out LifeYear))
                                                {
                                                    throw new Exception(dr["AssetCode"].ToString() + " can not parse [LifeYear] to double.");
                                                }

                                                int Days = Convert.ToInt32((modelDispose.DisposeDate.Value - LastCalDate).TotalDays);
                                                double DepreAmount = (Price / (LifeYear * 365)) * Days;

                                                if (Price <= DepreAmount + AccumDepreAmount)
                                                {
                                                    DepreAmount = Price - AccumDepreAmount - 1;
                                                }

                                                if (DepreAmount < 0)
                                                {
                                                    DepreAmount = 0;
                                                    continue;
                                                }

                                                if (string.IsNullOrEmpty(dr["DepreAccountId"].ToString()) || string.IsNullOrEmpty(dr["CumdepreAccountId"].ToString()))
                                                {
                                                    throw new Exception("ครุภัณฑ์รหัส " + dr["AssetCode"].ToString() + " ไม่มีการผูกบัญชีไว้");
                                                }

                                                Depre depreInfo = new Depre()
                                                {
                                                    BgmSourceSeq = dr["BgSourceId"].ToString(),
                                                    AccumDepreAccId = dr["CumdepreAccountId"].ToString(),
                                                    DepreAccId = dr["DepreAccountId"].ToString(),
                                                    Amount = DepreAmount,
                                                };

                                                depreList.Add(depreInfo);

                                                dbManager.CreateParameters(7);
                                                dbManager.AddParameters(0, "@AssetId", dr["AssetId"].ToString());
                                                dbManager.AddParameters(1, "@CalculatedDate", modelDispose.DisposeDate.Value);
                                                dbManager.AddParameters(2, "@DepAmount", DepreAmount);
                                                dbManager.AddParameters(3, "@TranGrpId", TranGrpSeq);
                                                dbManager.AddParameters(4, "@RecordStatus", "W");
                                                dbManager.AddParameters(5, "@CreatedBy", approvedByUserId);
                                                dbManager.AddParameters(6, "@CreatedWhen", DateTime.Now);

                                                strSQL = @"
INSERT INTO IcmAssetDepreciation
(AssetId, CalculatedDate, DepAmount, TranGrpId, RecordStatus, CreatedBy, CreatedWhen)
values
(@AssetId, @CalculatedDate, @DepAmount, @TranGrpId, @RecordStatus, @CreatedBy, @CreatedWhen)
";

                                                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                                            }
                                        }

                                        IList<Depre> depreList2 = depreList.GroupBy(g => new { g.AccumDepreAccId, g.DepreAccId })
                                            .Select(m => new Depre()
                                            {
                                                AccumDepreAccId = m.Key.AccumDepreAccId,
                                                DepreAccId = m.Key.DepreAccId,
                                                Amount = m.Sum(g => g.Amount),
                                            }).OrderBy(m => m.AccumDepreAccId).ToList();

                                        foreach (Depre depreInfo2 in depreList2)
                                        {
                                            if (depreInfo2.Amount > 0)
                                            {
                                                dbManager.CreateParameters(7);
                                                dbManager.AddParameters(0, "@tran_seq", strTranSeq1);
                                                dbManager.AddParameters(1, "@amount", depreInfo2.Amount);
                                                dbManager.AddParameters(2, "@record_status", "W");
                                                dbManager.AddParameters(3, "@created_by", approvedByUserId);
                                                dbManager.AddParameters(4, "@created_when", DateTime.Now);

                                                dbManager.AddParameters(5, "@account_seq1", depreInfo2.DepreAccId);
                                                dbManager.AddParameters(6, "@account_seq2", depreInfo2.AccumDepreAccId);
                                                strSQL = @"
INSERT INTO acm_transection_dtls
(tran_seq, account_seq, dr_or_cr, amount, record_status, created_by, created_when)
values
(@tran_seq, @account_seq, @dr_or_cr, @amount, @record_status, @created_by, @created_when)
";
                                                strSQL = strSQL.Replace("@dr_or_cr", "'Dr'");
                                                strSQL = strSQL.Replace("@account_seq", "@account_seq1");
                                                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                                                strSQL = strSQL.Replace("@dr_or_cr", "'Cr'");
                                                strSQL = strSQL.Replace("'Dr'", "'Cr'");
                                                strSQL = strSQL.Replace("@account_seq1", "@account_seq2");
                                                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                                            }
                                        }
                                    }
                                }
                                #endregion

                                #region bgm_source_seq 2
                                if (modelMovementHis2.Count > 0)
                                {
                                    string TranCode = GetNextTranCode("2", nBgYear.ToString(), "G", dbManager);
                                    int TranGrpSeq = GetNextTranGrpSeq(dbManager);
                                    string FiscalPeriod = GetFiscalPeriod(modelDispose.DisposeDate.Value);
                                    string strTranSeq1 = "";
                                    // have Depre
                                    int count = modelMovementHis2.Where(m => !m.Asset.IsLowerAsset).Count();
                                    count2 = count;
                                    if (count > 0)
                                    {
                                        depreList = new List<Depre>();
                                        strSQL = @"
insert into acm_transection
(tran_type, fiscal_period, tran_grp_seq, tran_code, tran_date
, bgm_year, work_area_seq, bgm_source_seq, ref_no, tran_name
, remarks, record_status, created_by, created_when)
values(
'G', @fiscal_period, @tran_grp_seq, @tran_code, @tran_date
, @bgm_year, @work_area_seq, @bgm_source_seq, @ref_no, @tran_name
, @remarks, @record_status, @created_by, @created_when
);
select scope_identity();
";

                                        dbManager.CreateParameters(13);

                                        dbManager.AddParameters(0, "@record_status", "W");
                                        dbManager.AddParameters(1, "@fiscal_period", FiscalPeriod);
                                        dbManager.AddParameters(2, "@tran_grp_seq", TranGrpSeq);
                                        dbManager.AddParameters(3, "@tran_code", TranCode);
                                        dbManager.AddParameters(4, "@tran_date", modelDispose.DisposeDate.Value);
                                        dbManager.AddParameters(5, "@bgm_year", nBgYear);
                                        dbManager.AddParameters(6, "@work_area_seq", null);
                                        dbManager.AddParameters(7, "@bgm_source_seq", 11);
                                        dbManager.AddParameters(8, "@ref_no", TranCode);
                                        dbManager.AddParameters(9, "@tran_name", "บันทึกค่าเสื่อมราคาจากการจำหน่าย [" + modelDispose.DisposeDesc + "]");//modelDispose.DisposeDesc);
                                        dbManager.AddParameters(10, "@remarks", modelDispose.Remark);
                                        dbManager.AddParameters(11, "@created_by", approvedByUserId);
                                        dbManager.AddParameters(12, "@created_when", DateTime.Now);

                                        strTranSeq1 = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();
                                    }

                                    for (int i = 0; i < modelMovementHis2.Count; i++)
                                    {
                                        // Update Recordstatus to "A" in IcmMovementHistory 
                                        var model = modelMovementHis2[i];
                                        model.UpdatedBy = approvedByUserId;
                                        model.UpdatedWhen = DateTime.Now;
                                        model.RecordStatus = Models.SYS.RecordStatus.ApprovedStatus;
                                        context.Entry(model).State = EntityState.Modified;

                                        // Update Recordstatus to "D" in IcmAsset 
                                        var modelAsset = context.Assets.Where(x => x.AssetId == model.AssetId).FirstOrDefault();
                                        if (modelAsset != null)
                                        {
                                            modelAsset.UpdatedBy = approvedByUserId;
                                            modelAsset.UpdatedWhen = DateTime.Now;
                                            modelAsset.RecordStatus = Models.SYS.RecordStatus.DeletedStatus;
                                            context.Entry(modelAsset).State = EntityState.Modified;
                                        }

                                        // Calculate Depre
                                        if (count > 0)
                                        {
                                            dbManager.CreateParameters(1);
                                            dbManager.AddParameters(0, "@AssetId", modelAsset.AssetId);

                                            strSQL = @"
select a.AssetId, a.AssetCode, a.IsLowerAsset
, a.AssetDate, a.Price, a.LifeYear, a.BgSourceId
, grp.AssetAccountId
, grp.DepreAccountId
, grp.CumdepreAccountId
, max(COALESCE(dep.CalculatedDate, a.AssetDate)) as CalculatedDate
, sum(COALESCE(dep.DepAmount, 0)) as DepAmount
from IcmAsset a
left join IcmAssetDepreciation dep on a.AssetId = dep.AssetId
inner join IcmMasResourceItem item on a.ResItemId = item.ResItemId
inner join IcmMasResourceSubGroup sub on item.ResSubGrpId = sub.ResSubGrpId
inner join IcmMasResourceGroup grp on sub.ResGrpId = grp.ResGrpId

where a.AssetId = @AssetId
and a.RecordStatus <> 'D'
and a.IsLowerAsset <> 1

group by a.AssetId, a.AssetCode, a.AssetDate, a.Price, a.LifeYear, a.BgSourceId, a.IsLowerAsset
, grp.AssetAccountId
, grp.DepreAccountId
, grp.CumdepreAccountId";
                                            DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                                            if (dtb.Rows.Count > 0)
                                            {
                                                DataRow dr = dtb.Rows[0];
                                                double Price = 0;// dr["Price"];
                                                double AccumDepreAmount = 0;// main.ConvertNullDouble(dr["DepAmount"]) ?? 0;
                                                double LifeYear = 0;// main.ConvertNullDouble(dr["LifeYear"]) ?? 0;
                                                DateTime LastCalDate = Convert.ToDateTime(dr["CalculatedDate"].ToString());

                                                if (!double.TryParse(dr["Price"].ToString(), out Price))
                                                {
                                                    throw new Exception(dr["AssetCode"].ToString() + " can not parse [Price] to double.");
                                                }

                                                if (!double.TryParse(dr["DepAmount"].ToString(), out AccumDepreAmount))
                                                {
                                                    throw new Exception(dr["AssetCode"].ToString() + " can not parse [DepAmount] to double.");
                                                }

                                                if (!double.TryParse(dr["LifeYear"].ToString(), out LifeYear))
                                                {
                                                    throw new Exception(dr["AssetCode"].ToString() + " can not parse [LifeYear] to double.");
                                                }

                                                int Days = Convert.ToInt32((modelDispose.DisposeDate.Value - LastCalDate).TotalDays);
                                                double DepreAmount = (Price / (LifeYear * 365)) * Days;

                                                if (Price <= DepreAmount + AccumDepreAmount)
                                                {
                                                    DepreAmount = Price - AccumDepreAmount - 1;
                                                }

                                                if (DepreAmount < 0)
                                                {
                                                    DepreAmount = 0;
                                                    continue;
                                                }

                                                if (string.IsNullOrEmpty(dr["DepreAccountId"].ToString()) || string.IsNullOrEmpty(dr["CumdepreAccountId"].ToString()))
                                                {
                                                    throw new Exception("ครุภัณฑ์รหัส " + dr["AssetCode"].ToString() + " ไม่มีการผูกบัญชีไว้");
                                                }

                                                Depre depreInfo = new Depre()
                                                {
                                                    BgmSourceSeq = dr["BgSourceId"].ToString(),
                                                    AccumDepreAccId = dr["CumdepreAccountId"].ToString(),
                                                    DepreAccId = dr["DepreAccountId"].ToString(),
                                                    Amount = DepreAmount,
                                                };

                                                depreList.Add(depreInfo);

                                                dbManager.CreateParameters(7);
                                                dbManager.AddParameters(0, "@AssetId", dr["AssetId"].ToString());
                                                dbManager.AddParameters(1, "@CalculatedDate", modelDispose.DisposeDate.Value);
                                                dbManager.AddParameters(2, "@DepAmount", DepreAmount);
                                                dbManager.AddParameters(3, "@TranGrpId", TranGrpSeq);
                                                dbManager.AddParameters(4, "@RecordStatus", "W");
                                                dbManager.AddParameters(5, "@CreatedBy", approvedByUserId);
                                                dbManager.AddParameters(6, "@CreatedWhen", DateTime.Now);

                                                strSQL = @"
INSERT INTO IcmAssetDepreciation
(AssetId, CalculatedDate, DepAmount, TranGrpId, RecordStatus, CreatedBy, CreatedWhen)
values
(@AssetId, @CalculatedDate, @DepAmount, @TranGrpId, @RecordStatus, @CreatedBy, @CreatedWhen)
";

                                                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                                            }
                                        }

                                        IList<Depre> depreList2 = depreList.GroupBy(g => new { g.AccumDepreAccId, g.DepreAccId })
                                            .Select(m => new Depre()
                                            {
                                                AccumDepreAccId = m.Key.AccumDepreAccId,
                                                DepreAccId = m.Key.DepreAccId,
                                                Amount = m.Sum(g => g.Amount),
                                            }).OrderBy(m => m.AccumDepreAccId).ToList();

                                        foreach (Depre depreInfo2 in depreList2)
                                        {
                                            if (depreInfo2.Amount > 0)
                                            {
                                                dbManager.CreateParameters(7);
                                                dbManager.AddParameters(0, "@tran_seq", strTranSeq1);
                                                dbManager.AddParameters(1, "@amount", depreInfo2.Amount);
                                                dbManager.AddParameters(2, "@record_status", "W");
                                                dbManager.AddParameters(3, "@created_by", approvedByUserId);
                                                dbManager.AddParameters(4, "@created_when", DateTime.Now);

                                                dbManager.AddParameters(5, "@account_seq1", depreInfo2.DepreAccId);
                                                dbManager.AddParameters(6, "@account_seq2", depreInfo2.AccumDepreAccId);
                                                strSQL = @"
INSERT INTO acm_transection_dtls
(tran_seq, account_seq, dr_or_cr, amount, record_status, created_by, created_when)
values
(@tran_seq, @account_seq, @dr_or_cr, @amount, @record_status, @created_by, @created_when)
";
                                                strSQL = strSQL.Replace("@dr_or_cr", "'Dr'");
                                                strSQL = strSQL.Replace("@account_seq", "@account_seq1");
                                                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                                                strSQL = strSQL.Replace("@dr_or_cr", "'Cr'");
                                                strSQL = strSQL.Replace("'Dr'", "'Cr'");
                                                strSQL = strSQL.Replace("@account_seq1", "@account_seq2");
                                                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                                            }
                                        }
                                    }
                                }
                                #endregion

                                dbManager.CommitTransaction();
                            }
                            catch (Exception ex)
                            {
                                dbManager.RollBackTransaction();
                                throw ex;
                            }
                            finally
                            {
                                dbManager.Dispose();
                            }

                            #region insert final transaction

                            try
                            {
                                dbManager.Open();
                                dbManager.BeginTransaction();
                                strSQL = @"
select dispose_account_seq from acm_mas_base_account;
";
                                string dispose_account_seq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();

                                #region bgm_source_seq 1

                                if (count1 > 0)
                                {
                                    string TranCode = GetNextTranCode("1", nBgYear.ToString(), "G", dbManager);
                                    int TranGrpSeq = GetNextTranGrpSeq(dbManager);
                                    string FiscalPeriod = GetFiscalPeriod(modelDispose.DisposeDate.Value);

                                    strSQL = @"
insert into acm_transection
(tran_type, fiscal_period, tran_grp_seq, tran_code, tran_date
, bgm_year, work_area_seq, bgm_source_seq, ref_no, tran_name
, remarks, record_status, created_by, created_when)
values(
'G', @fiscal_period, @tran_grp_seq, @tran_code, @tran_date
, @bgm_year, @work_area_seq, @bgm_source_seq, @ref_no, @tran_name
, @remarks, @record_status, @created_by, @created_when
);
select scope_identity();
";

                                    dbManager.CreateParameters(13);
                                    dbManager.AddParameters(0, "@record_status", "W");
                                    dbManager.AddParameters(1, "@fiscal_period", FiscalPeriod);
                                    dbManager.AddParameters(2, "@tran_grp_seq", TranGrpSeq);
                                    dbManager.AddParameters(3, "@tran_code", TranCode);
                                    dbManager.AddParameters(4, "@tran_date", modelDispose.DisposeDate.Value);
                                    dbManager.AddParameters(5, "@bgm_year", nBgYear);
                                    dbManager.AddParameters(6, "@work_area_seq", null);
                                    dbManager.AddParameters(7, "@bgm_source_seq", 1);
                                    dbManager.AddParameters(8, "@ref_no", TranCode);
                                    dbManager.AddParameters(9, "@tran_name", "" + modelDispose.DisposeDesc);
                                    dbManager.AddParameters(10, "@remarks", modelDispose.Remark);
                                    dbManager.AddParameters(11, "@created_by", approvedByUserId);
                                    dbManager.AddParameters(12, "@created_when", DateTime.Now);

                                    string strTranSeq1 = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();

                                    var Depres = modelMovementHis1.Join(context.AssetDepreciations.Where(m => m.RecordStatus != "D"),
                                        a => a.AssetId,
                                        b => b.AssetId,
                                        (a, b) => new
                                        {
                                            AssetAccountId = a.Asset.MasResourceItem.MasResourceSubGroup.MasResourceGroup.AssetAccountId,
                                            CumdepreAccountId = a.Asset.MasResourceItem.MasResourceSubGroup.MasResourceGroup.CumdepreAccountId,
                                            Amount = a.Asset.Price,
                                            DepAmount = b.DepAmount
                                        }
                                        )
                                        .GroupBy(m => new { m.CumdepreAccountId, m.AssetAccountId, m.Amount })
                                        .Select(m => new
                                        {
                                            CumdepreAccountId = m.Key.CumdepreAccountId,
                                            AssetAccountId = m.Key.AssetAccountId,
                                            Amount = m.Key.Amount,
                                            DepAmount = m.Sum(g => g.DepAmount)
                                        });

                                    strSQL = @"
INSERT INTO acm_transection_dtls
(tran_seq, account_seq, dr_or_cr, amount, record_status, created_by, created_when)
values
(@tran_seq, @account_seq, @dr_or_cr, @amount, @record_status, @created_by, @created_when)
";

                                    foreach (var Depre in Depres)
                                    {
                                        dbManager.CreateParameters(7);
                                        dbManager.AddParameters(0, "@tran_seq", strTranSeq1);
                                        dbManager.AddParameters(1, "@amount", Depre.DepAmount);
                                        dbManager.AddParameters(2, "@record_status", "W");
                                        dbManager.AddParameters(3, "@created_by", approvedByUserId);
                                        dbManager.AddParameters(4, "@created_when", DateTime.Now);
                                        dbManager.AddParameters(5, "@account_seq", Depre.CumdepreAccountId);
                                        dbManager.AddParameters(6, "@dr_or_cr", "Dr");
                                        dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                                        dbManager.AddParameters(0, "@tran_seq", strTranSeq1);
                                        dbManager.AddParameters(1, "@amount", Depre.Amount - Convert.ToDouble(Depre.DepAmount));
                                        dbManager.AddParameters(2, "@record_status", "W");
                                        dbManager.AddParameters(3, "@created_by", approvedByUserId);
                                        dbManager.AddParameters(4, "@created_when", DateTime.Now);
                                        dbManager.AddParameters(5, "@account_seq", dispose_account_seq);
                                        dbManager.AddParameters(6, "@dr_or_cr", "Dr");
                                        dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                                        dbManager.AddParameters(0, "@tran_seq", strTranSeq1);
                                        dbManager.AddParameters(1, "@amount", Depre.Amount);
                                        dbManager.AddParameters(2, "@record_status", "W");
                                        dbManager.AddParameters(3, "@created_by", approvedByUserId);
                                        dbManager.AddParameters(4, "@created_when", DateTime.Now);
                                        dbManager.AddParameters(5, "@account_seq", Depre.AssetAccountId);
                                        dbManager.AddParameters(6, "@dr_or_cr", "Cr");
                                        dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                                    }

                                }
                                #endregion
                                #region bgm_source_seq 2
                                if (count2 > 0)
                                {
                                    string TranCode = GetNextTranCode("1", nBgYear.ToString(), "G", dbManager);
                                    int TranGrpSeq = GetNextTranGrpSeq(dbManager);
                                    string FiscalPeriod = GetFiscalPeriod(modelDispose.DisposeDate.Value);

                                    strSQL = @"
insert into acm_transection
(tran_type, fiscal_period, tran_grp_seq, tran_code, tran_date
, bgm_year, work_area_seq, bgm_source_seq, ref_no, tran_name
, remarks, record_status, created_by, created_when)
values(
'G', @fiscal_period, @tran_grp_seq, @tran_code, @tran_date
, @bgm_year, @work_area_seq, @bgm_source_seq, @ref_no, @tran_name
, @remarks, @record_status, @created_by, @created_when
);
select scope_identity();
";
                                    dbManager.CreateParameters(13);
                                    dbManager.AddParameters(0, "@record_status", "W");
                                    dbManager.AddParameters(1, "@fiscal_period", FiscalPeriod);
                                    dbManager.AddParameters(2, "@tran_grp_seq", TranGrpSeq);
                                    dbManager.AddParameters(3, "@tran_code", TranCode);
                                    dbManager.AddParameters(4, "@tran_date", modelDispose.DisposeDate.Value);
                                    dbManager.AddParameters(5, "@bgm_year", nBgYear);
                                    dbManager.AddParameters(6, "@work_area_seq", null);
                                    dbManager.AddParameters(7, "@bgm_source_seq", 11);
                                    dbManager.AddParameters(8, "@ref_no", TranCode);
                                    dbManager.AddParameters(9, "@tran_name", "" + modelDispose.DisposeDesc);
                                    dbManager.AddParameters(10, "@remarks", modelDispose.Remark);
                                    dbManager.AddParameters(11, "@created_by", approvedByUserId);
                                    dbManager.AddParameters(12, "@created_when", DateTime.Now);

                                    string strTranSeq1 = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();

                                    var Depres = modelMovementHis2.Join(context.AssetDepreciations.Where(m => m.RecordStatus != "D"),
                                        a => a.AssetId,
                                        b => b.AssetId,
                                        (a, b) => new
                                        {
                                            AssetAccountId = a.Asset.MasResourceItem.MasResourceSubGroup.MasResourceGroup.AssetAccountId,
                                            CumdepreAccountId = a.Asset.MasResourceItem.MasResourceSubGroup.MasResourceGroup.CumdepreAccountId,
                                            Amount = a.Asset.Price,
                                            DepAmount = b.DepAmount
                                        }
                                        )
                                        .GroupBy(m => new { m.CumdepreAccountId, m.AssetAccountId, m.Amount })
                                        .Select(m => new
                                        {
                                            CumdepreAccountId = m.Key.CumdepreAccountId,
                                            AssetAccountId = m.Key.AssetAccountId,
                                            Amount = m.Key.Amount,
                                            DepAmount = m.Sum(g => g.DepAmount)
                                        });

                                    strSQL = @"
INSERT INTO acm_transection_dtls
(tran_seq, account_seq, dr_or_cr, amount, record_status, created_by, created_when)
values
(@tran_seq, @account_seq, @dr_or_cr, @amount, @record_status, @created_by, @created_when)
";

                                    foreach (var Depre in Depres)
                                    {
                                        dbManager.CreateParameters(7);
                                        dbManager.AddParameters(0, "@tran_seq", strTranSeq1);
                                        dbManager.AddParameters(1, "@amount", Depre.DepAmount);
                                        dbManager.AddParameters(2, "@record_status", "W");
                                        dbManager.AddParameters(3, "@created_by", approvedByUserId);
                                        dbManager.AddParameters(4, "@created_when", DateTime.Now);
                                        dbManager.AddParameters(5, "@account_seq", Depre.CumdepreAccountId);
                                        dbManager.AddParameters(6, "@dr_or_cr", "Dr");
                                        dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                                        dbManager.AddParameters(0, "@tran_seq", strTranSeq1);
                                        dbManager.AddParameters(1, "@amount", Depre.Amount - Convert.ToDouble(Depre.DepAmount));
                                        dbManager.AddParameters(2, "@record_status", "W");
                                        dbManager.AddParameters(3, "@created_by", approvedByUserId);
                                        dbManager.AddParameters(4, "@created_when", DateTime.Now);
                                        dbManager.AddParameters(5, "@account_seq", dispose_account_seq);
                                        dbManager.AddParameters(6, "@dr_or_cr", "Dr");
                                        dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                                        dbManager.AddParameters(0, "@tran_seq", strTranSeq1);
                                        dbManager.AddParameters(1, "@amount", Depre.Amount);
                                        dbManager.AddParameters(2, "@record_status", "W");
                                        dbManager.AddParameters(3, "@created_by", approvedByUserId);
                                        dbManager.AddParameters(4, "@created_when", DateTime.Now);
                                        dbManager.AddParameters(5, "@account_seq", Depre.AssetAccountId);
                                        dbManager.AddParameters(6, "@dr_or_cr", "Cr");
                                        dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                                    }

                                }
                                #endregion
                                dbManager.CommitTransaction();
                                
                            }
                            catch (Exception ex)
                            {
                                dbManager.RollBackTransaction();
                                throw ex;
                            }
                            finally
                            {
                                dbManager.Dispose();
                            }
                            #endregion
                        }
                    }

                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public string GetFiscalPeriod(DateTime dt)
        {
            string strFiscalPeriod = dt.Year.ToString() + "/" + dt.Month.ToString();

            return strFiscalPeriod;
        }

        public int GetNextTranGrpSeq(DBManager dbManager)
        {
            //DBManager dbManager = new DBManager(DataProvider.SqlServer, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
            DataTable dtb = null;
            int nNextTranGrpSeq = 1;
            try
            {

                string strSQL = "SELECT max(coalesce(tran_grp_seq, 0)) as tran_grp_seq"
                    + " from acm_transection"
                    + " where record_status not like 'D'";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0)
                {
                    if (dtb.Rows[0]["tran_grp_seq"] != DBNull.Value)
                    {
                        nNextTranGrpSeq = Convert.ToInt32(dtb.Rows[0]["tran_grp_seq"]) + 1;
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            //finally
            //{
            //    dbManager.Dispose();
            //}
            return nNextTranGrpSeq;
        }

        public string GetNextTranCode(string bgm_source_seq, string bgm_year, string tran_type, DBManager dbManager)
        {
            //DBManager dbManager = new DBManager(DataProvider.SqlServer, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
            DataTable dtb = null;
            string strNextTranCode = tran_type + "-" + (!string.IsNullOrEmpty(bgm_source_seq) ? (bgm_source_seq == "1" ? "1" : "2") : "3") + "-" + bgm_year + "-00001";
            try
            {

                string strSQL = "SELECT max(CONVERT(VARCHAR(50),tran_code)) as tran_code"
                    + " from acm_transection"
                    + " where record_status not like 'D'"
                    + " and bgm_source_seq = " + (!string.IsNullOrEmpty(bgm_source_seq) ? bgm_source_seq : "1")
                    + " and bgm_year = " + bgm_year
                    + " and tran_type = '" + tran_type + "'"

                    + " group by bgm_source_seq, bgm_year, tran_type";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0)
                {
                    if (dtb.Rows[0]["tran_code"] != DBNull.Value)
                    {
                        strNextTranCode = tran_type + "-" + (!string.IsNullOrEmpty(bgm_source_seq) ? (bgm_source_seq == "1" ? "1" : "2") : "3") + "-" + bgm_year + "-" + (Convert.ToInt32(dtb.Rows[0]["tran_code"].ToString().Split('-')[3]) + 1).ToString("00000");
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            //finally
            //{
            //    dbManager.Dispose();
            //}
            return strNextTranCode;
        }

        #endregion

        // แสดงใบจำหน่าย จากตาราง IcmDispose
        public IEnumerable<Dispose> SelectDispose(Dispose criteriaModel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<Dispose> query = context.Disposes;

                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    return query.ToList();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        // แสดงรายการวัสดุ เพื่อที่จะเลือกจำหน่าย
        public IEnumerable<OfficeSuppliesLot> SelectOfficeSuppliesLot(int ResTypeId, int? FromWhId, List<string> includeSubObjectString = null)
        {
            try
            {
                var context = new GERPContext(false);

                // เลือกรายการวัสดุ ตามใบตรวจนับ(IcmInventoryStockTakingItems)
                IQueryable<OfficeSuppliesLot> QueryOfficesupplieslot = (from a in context.OfficeSuppliesLots
                                         join b in context.InventoryStockTakingItems on a.ResItemId equals b.ResItemId
                                         join c in context.InventoryStockTakings on b.StockTakingId equals c.StockTakingId
                                         where a.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                                         && b.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                                         && c.RecordStatus == Models.SYS.RecordStatus.ApprovedStatus
                                         && c.ResourceTypeId == ResTypeId
                                         && ( FromWhId == null? a.WhId == null : a.WhId == FromWhId )
                                         select a);

                if (includeSubObjectString != null)
                {
                    QueryOfficesupplieslot = includeSubObjectString.Aggregate(QueryOfficesupplieslot, (current, includePath) => current.Include(includePath));
                }

                // เลือกรายการวัสดุ ในตาราง IcmDisposeOfficeSupplylotHistory
                var MovementHistory = (from a in context.DisposeOfficeSupplylotHistorys
                                       join b in context.OfficeSuppliesLots on a.OfficeSuppliesLotId equals b.OfficeSuppliesLotId
                                       where a.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                                       && b.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                                       select b).ToList();

                var ListOfficesupplieslot = QueryOfficesupplieslot.ToList();

                // เลือกรายการวัสดุ โดยตัดวัสดุที่อยุ่ในตาราง IcmDisposeOfficeSupplylotHistory ออก
                var OfficesupplieslotExpect = ListOfficesupplieslot.Except(MovementHistory).ToList();

                return OfficesupplieslotExpect.ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        // แสดงรายการครุภัณฑ์ เพื่อที่จะเลือกจำหน่าย
        public IEnumerable<Asset> SelectAsset(int ResTypeId, int? FromWhId, int? FromJobsiteId, List<string> includeSubObjectString = null)
        {
            try
            {
                var context = new GERPContext(false);

                // เลือกรายการครุภัณฑ์ ตามใบตรวจนับ(IcmInventoryStockTakingItems)
                IQueryable<Asset> QueryAsset = (from a in context.Assets
                                              join b in context.InventoryStockTakingItems.DefaultIfEmpty() on a.ResItemId equals b.ResItemId
                                                join c in context.InventoryStockTakings.DefaultIfEmpty() on b.StockTakingId equals c.StockTakingId
                                              where a.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                                              && b.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                                              && c.RecordStatus == Models.SYS.RecordStatus.ApprovedStatus
                                              && c.ResourceTypeId == ResTypeId
                                              // && (FromWhId == null ? a.WhId == null : a.WhId == FromWhId)
                                              // && (FromJobsiteId == null ? a.JobSiteId == null : a.JobSiteId == FromJobsiteId)
                                              select a);
                
                if (includeSubObjectString != null)
                {
                    QueryAsset = includeSubObjectString.Aggregate(QueryAsset, (current, includePath) => current.Include(includePath));
                }

                // เลือกรายการครุภัณฑ์ ในตาราง IcmMovementHistory โดยที่ MovementType = "Dispose"
                var MovementHistory = (from a in context.MovementHistorys
                                       join b in context.Assets on a.AssetId equals b.AssetId
                                       where a.MovementType == "Dispose"
                                       where a.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                                       && b.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                                       select b).ToList();

                var ListAsset = QueryAsset.ToList();

                // เลือกรายการครุภัณฑ์ โดยตัดครุภัณฑ์ที่อยุ่ในตาราง IcmMovementHistory ออก
                var AssetExpect = ListAsset.Except(MovementHistory).ToList();

                return AssetExpect;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        // แสดงรายการ Item ที่ได้เลือกจำหน่ายไปแล้ว  จากตาราง IcmDisposeOfficeSupplylotHistory  >>>  กรณีที่พัสดุเป็น "วัสดุ"
        public IEnumerable<DisposeOfficeSupplylotHistory> SelectDisposeOfficeSupplylotHistory(DisposeOfficeSupplylotHistory criteriaModel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<DisposeOfficeSupplylotHistory> query = context.DisposeOfficeSupplylotHistorys;

                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    return query.ToList();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        // แสดงรายการ Item ที่ได้เลือกจำหน่ายไปแล้ว  จากตาราง IcmMovementHistory  >>>  กรณีที่พัสดุเป็น "ครุภัณฑ์"
        public IEnumerable<MovementHistory> SelectMovementHistory(MovementHistory criteriaModel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<MovementHistory> query = context.MovementHistorys;

                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    return query.ToList();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        // ยกเลิกรายการพัสดุที่เลือก
        public void UnSelect(int id, string ResourceType, int updateByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    if (ResourceType == "วัสดุ")
                    {
                        var model = context.DisposeOfficeSupplylotHistorys.Where(x => x.DisposeOfficeSupplylotHisId == id).FirstOrDefault();
                        model.UpdatedBy = updateByUserId;
                        model.UpdatedWhen = DateTime.Now;
                        model.RecordStatus = Models.SYS.RecordStatus.DeletedStatus;
                        context.Entry(model).State = EntityState.Modified;
                        context.SaveChanges();
                    }
                    else 
                    {
                        var model = context.MovementHistorys.Where(x => x.MoveHistId == id).FirstOrDefault();
                        model.UpdatedBy = updateByUserId;
                        model.UpdatedWhen = DateTime.Now;
                        model.RecordStatus = Models.SYS.RecordStatus.DeletedStatus;
                        context.Entry(model).State = EntityState.Modified;
                        context.SaveChanges();
                    }
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }

        // ตรวจสอบว่าใบจำหน่ายแต่ละใบว่ามี "คณะกรรมการจำหน่ายพัสดุ" แล้วหรือยัง
        public bool IsCommittee(int DisposeId)
        {
             try
            {
                using (var context = new GERPContext())
                {
                    bool IsCommittee = false;

                    // ComFunctionId == 75  >>>   75 คือ คณะกรรมการจำหน่ายพัสดุ   มาจาก LookUpId ในตาราง IcmMasLookup 
                    //var model = context.Committees.Where(x => x.RefId == DisposeId && x.ComFunctionId == 75).ToList();
                    var dispost = context.Disposes.Find(DisposeId);
                    var model = context.Committees.Where(x => x.CommitteeGroupId == dispost.CommitteeGroupId && x.ComFunctionId == 75).ToList();
                    
                    if (model.Count > 0)
                    {
                        IsCommittee = true;
                    }

                    return IsCommittee;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
           
        }
    }

    public class Depre
    {
        public string BgmSourceSeq { get; set; }
        public string AccumDepreAccId { get; set; }
        public string DepreAccId { get; set; }
        public double Amount { get; set; }
    }
}
